﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;

namespace BusinessLayer
{
    public class ClsFinancialYear
    {
        public int FinancialYearID { get; set; }
       
        public DateTime? StartDay { get; set; }
        public DateTime? EndDay { get; set; }
        public Boolean? chkActive { get; set; }
       

        public DataTable GetAllFinancialYear()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("Select * from FinacialYear", "FinacialYear");
            }
        }
        public void GetFinancialYear(int intFinancialYearID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select * from FinacialYear where FinancialYearID=" + intFinancialYearID.ToString(), "FinancialYear");
                if (dt.Rows.Count > 0)
                {
                    FinancialYearID = Convert.ToInt32(dt.Rows[0]["FinancialYearID"]);
                    StartDay = Convert.ToDateTime(dt.Rows[0]["StartDay"]);
                    EndDay = Convert.ToDateTime(dt.Rows[0]["EndDay"]);
                    chkActive = Convert.ToBoolean(dt.Rows[0]["Active"]);
                   
                }
            }
        }
        public bool SaveFinancialYear(string KeyForSave)
        {
            using (OleDataClient data = new OleDataClient())
            {
                if (KeyForSave == "add")
                {

                    try
                    {

                        string count = data.GetValue("select count(*) from FinacialYear where  Format([StartDay],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(StartDay) + "'  and '" + Utility.GetDateYYYYMMDD(StartDay) + "' or Format([EndDay],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(EndDay) + "'  and '" + Utility.GetDateYYYYMMDD(EndDay) + "'");
                        if (Convert.ToInt32(count) > 0)
                        {
                            throw new Exception("Financial year period already exist");
                        }
                        else
                        {
                            data.StartTran();
                            data.ExecuteNonQuery("Insert into FinacialYear(StartDay,EndDay,Active) values('" + StartDay + "','" + EndDay + "'" + "," + chkActive + " )");
                            string autoID = data.GetValue("SELECT @@IDENTITY");
                            //Check whether any other Data in FinacialYear table ha Active status
                            if (chkActive == true)
                            {
                                data.ExecuteNonQuery("Update FinacialYear set Active= fase where FinancialYearID NOT IN =" + autoID.ToString());
                            }


                        }
                    }

                    catch(Exception ex)
                     {
                        data.RollBackTran();
                        throw ex;
                    }

                }
                else
                {
                    string count = data.GetValue("select count(*) from FinacialYear where  Format([StartDay],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(StartDay) + "'  and '" + Utility.GetDateYYYYMMDD(StartDay) + "' or Format([EndDay],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(EndDay) + "'  and '" + Utility.GetDateYYYYMMDD(EndDay) + "'");
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Financial year period already exist");
                    }
                    else
                    {
                    data.ExecuteNonQuery("Update FinacialYear set StartDay='" + StartDay + "',"  + " EndDay='" +  EndDay + "',"  +  " Active =" + chkActive + " where FinancialYearID=" + FinancialYearID.ToString());
                    }

                }
            }
            return true;
        }
        public void DeleteFinancialYear()
        {
            using (OleDataClient data = new OleDataClient())
            {
                data.ExecuteNonQuery("Delete from  FinacialYear where FinancialYearID=" + FinancialYearID.ToString());
            }
        }
    }



}